/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.TActividadVO;
import com.informet.ipd.modelo.vo.TActividadMVO;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class TActividadDao extends DBase {

    Connection conn = null;

    public TActividadDao() {
   super();
    }



    private void setTActividad(TActividadVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODATAREA_PK(rs.getInt("NUM_CODATAREA_PK"));
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setTX_DESCATAREA(rs.getString("TX_DESCATAREA"));
        bean.setDT_INIATAREA(rs.getString("DT_INIATAREA"));
        bean.setDT_TERATAREA(rs.getString("DT_TERATAREA"));
        bean.setTX_RESPATAREA(rs.getString("TX_RESPATAREA"));
        bean.setTX_UMAATAREA(rs.getString("TX_UMAATAREA"));
        bean.setTX_TIPOATAREA1(rs.getInt("TX_TIPOATAREA1"));
   
    }




    private void setTActividadmax(TActividadVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODATAREA_PK(rs.getInt("NUM_CODATAREA_PK"));
    }


    private void setSuma(TActividadVO bean, ResultSet rs) throws SQLException {
        bean.setSuma(rs.getInt("sumta"));
    }


    private void setTareaPorActividad (TActividadVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODATAREA_PK(rs.getInt("NUM_CODATAREA_PK"));
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setTX_DESCATAREA(rs.getString("TX_DESCATAREA"));
        bean.setDT_INIATAREA(rs.getString("DT_INIATAREA"));
        bean.setDT_TERATAREA(rs.getString("DT_TERATAREA"));
        bean.setTX_RESPATAREA(rs.getString("TX_RESPATAREA"));
        bean.setTX_UMAATAREA(rs.getString("TX_UMAATAREA"));
        bean.setTX_TIPOATAREA1(rs.getInt("TX_TIPOATAREA1"));
        bean.setNUM_PPTO_ACT(rs.getDouble("NUM_PRESUACTPER"));
        bean.setNUM_PRESUATAREA(rs.getBigDecimal("NUM_PRESUATAREA"));
        bean.setNUM_CANTATAREA(rs.getInt("NUM_CANTATAREA"));
    }

    private void setTareaPorActividadM (TActividadVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_CODATAREA_PK(rs.getInt("NUM_CODATAREA_PK"));
        bean.setNUM_CODACTPER_PK(rs.getInt("NUM_CODACTPER_PK"));
        bean.setTX_DESCATAREA(rs.getString("TX_DESCATAREA"));
        bean.setDT_INIATAREA(rs.getString("DT_INIATAREA"));
        bean.setDT_TERATAREA(rs.getString("DT_TERATAREA"));
        bean.setTX_RESPATAREA(rs.getString("TX_RESPATAREA"));
        bean.setTX_UMAATAREA(rs.getString("TX_UMAATAREA"));
        bean.setTX_TIPOATAREA1(rs.getInt("TX_TIPOATAREA1"));
        bean.setNUM_PPTO_ACT(rs.getDouble("NUM_PRESUACTPER"));
        bean.setNUM_PRESUATAREA(rs.getBigDecimal("NUM_PRESUATAREA"));
    }




    public String spMan(int tipo,TActividadVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call manATarea(?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODATAREA_PK());
            cs.setString(3, bean.getTX_DESCATAREA());
            
            cs.setString(4, bean.getDT_INIATAREA());
            
            cs.setString(5, bean.getDT_TERATAREA());
            
            cs.setString(6, bean.getTX_RESPATAREA());
            cs.setString(7, bean.getTX_UMAATAREA());
            cs.setInt(8, bean.getNUM_CANTATAREA());
            cs.setInt(9, bean.getTX_TIPOATAREA1());
            cs.setInt(10, bean.getNUM_CODACTPER_PK());
            cs.setBigDecimal(11, bean.getNUM_PRESUATAREA());
            
            cs.execute();
        } catch (Exception e) {
            msj = e.toString();
            JOptionPane.showMessageDialog(null,"procedimiento :"+ e.toString());
            return msj;
        }
        return msj;
    }



     public String spManM(int tipo,TActividadVO bean) {
        String msj = "";
        try {
        	conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call manATareaM(?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODATAREA_PK());
            cs.setString(3, bean.getTX_DESCATAREA());
            cs.setString(4, bean.getDT_INIATAREA());
            cs.setString(5, bean.getDT_TERATAREA());
            cs.setString(6, bean.getTX_RESPATAREA());
            cs.setString(7, bean.getTX_UMAATAREA());
            cs.setInt(8, bean.getTX_TIPOATAREA1());
            cs.setInt(9, bean.getNUM_CODACTPER_PK());
            cs.execute();
        }
        catch (Exception e) {
            msj = e.toString();
            JOptionPane.showMessageDialog(null,"procedimiento :"+ e.toString());
            return msj;
        }
        return msj;
    }


     /*public String spManMo(int tipo,TActividadMVO bean) {
        String msj = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call manATareaMo(?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setInt(2, bean.getNUM_CODATAREAM_PK());
            JOptionPane.showMessageDialog(null, "ingresando codactm"+bean.getNUM_CODATAREAM_PK());
            cs.setString(3, bean.getTX_DESCATAREAM());
            cs.setString(4, bean.getDT_INIATAREAM());
            cs.setString(5, bean.getDT_TERATAREAM());
            cs.setString(6, bean.getTX_RESPATAREAM());
            cs.setString(7, bean.getTX_UMAATAREAM());
            cs.setInt(8, bean.getTX_TIPOATAREAM1());
            cs.setInt(9, bean.getNUM_CODACTPER_PK());
            cs.execute();
        }
        catch (Exception e) {
            msj = e.toString();
            JOptionPane.showMessageDialog(null,"procedimiento :"+ e.toString());
            return msj;
        }
        return msj;
    }*/

    /*public ArrayList<FisiMetaVO> selectLista() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<OEstrategicoVO> lista = new ArrayList<OEstrategicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODOBJ_PK,TX_DESCOBJ from ME_OBJ";
            rs = stmt.executeQuery(sql);
            OEstrategicoVO bean = null;
            while (rs.next()) {
                bean = new OEstrategicoVO();
                setOEstrategico(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }*/

    public List getlist() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select NUM_CODATAREA_PK,NUM_CODACTPER_PK,TX_DESCATAREA, convert(VARCHAR(24) , DT_INIATAREA,103) AS DT_INIATAREA ,convert(VARCHAR(24),DT_TERATAREA,103) AS DT_TERATAREA,TX_RESPATAREA, TX_UMAATAREA, TX_TIPOATAREA1 from ME_ATAREA";



            rs = stmt.executeQuery(sql);
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setTActividad(bean, rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }






    public int maximo() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        String sql = "";
        int max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select max(NUM_CODATAREA_PK) AS NUM_CODATAREA_PK FROM ME_ATAREA";
            rs = stmt.executeQuery(sql);
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setTActividadmax(bean, rs);
                max=bean.getNUM_CODATAREA_PK();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }


    public List getFiltro(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            sql = "select NUM_CODATAREA_PK,NUM_CODACTPER_PK,TX_DESCATAREA, convert(VARCHAR(24) , DT_INIATAREA,103) AS DT_INIATAREA ,convert(VARCHAR(24),DT_TERATAREA,103) AS DT_TERATAREA,TX_RESPATAREA, TX_UMAATAREA, TX_TIPOATAREA1 from ME_ATAREA where NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setTActividad(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public List getListById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
         

            sql = "select NUM_CODATAREA_PK,NUM_CODACTPER_PK,TX_DESCATAREA, convert(VARCHAR(24) , DT_INIATAREA,103) AS DT_INIATAREA ,convert(VARCHAR(24),DT_TERATAREA,103) AS DT_TERATAREA,TX_RESPATAREA, TX_UMAATAREA, TX_TIPOATAREA1,TX_TIPOATAREA2 from ME_ATAREA where NUM_CODATAREA_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setTActividad(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public double sumaM(int id,int id2) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        //lista=null;
        String sql = "";
        double max=0;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "select sum(NUM_PRESUATAREA) as sumta from ME_ATAREA inner join dbo.ME_ACTPER on dbo.ME_ACTPER.NUM_CODACTPER_PK=dbo.ME_ATAREA.NUM_CODACTPER_PK where NUM_CODDEPEN_PK=? AND ME_ACTPER.NUM_CODACTPER_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setInt(2, id2);
            rs = pstmt.executeQuery();
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setSuma(bean, rs);
                max=bean.getSuma();
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return max;
    }





    public List getTareaPorActividad(int idAct) throws SQLException {
    	
    	 
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
        	conn = DriverManager.getConnection(url, usuario, password);
            sql = "select ac.NUM_PRESUACTPER ,t.NUM_CODATAREA_PK,t.NUM_CODACTPER_PK,t.TX_DESCATAREA,isnull(t.NUM_CANTATAREA,0) as NUM_CANTATAREA, convert(VARCHAR(24) , t.DT_INIATAREA,103) AS DT_INIATAREA ,convert(VARCHAR(24),t.DT_TERATAREA,103) AS DT_TERATAREA,t.TX_RESPATAREA,t.TX_UMAATAREA,t.TX_TIPOATAREA1,t.NUM_PRESUATAREA from ME_ATAREA t inner join ME_ACTPER ac on ac.NUM_CODACTPER_PK=t.NUM_CODACTPER_PK where t.NUM_CODACTPER_PK=?";
           
            pstmt = conn.prepareStatement(sql);
          
            pstmt.setInt(1, idAct);
         
            rs = pstmt.executeQuery();
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setTareaPorActividad(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
    
        return lista;
    }

    public List getTareaPorActividadM(int idAct) throws SQLException {


        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TActividadVO> lista = new ArrayList<TActividadVO>(0);
        //lista=null;
        String sql = "";
        try {
        	conn = DriverManager.getConnection(url, usuario, password);
            sql = "select ac.NUM_PRESUACTPER ,t.NUM_CODATAREA_PK,t.NUM_CODACTPER_PK,t.TX_DESCATAREA, convert(VARCHAR(24) , t.DT_INIATAREA,103) AS DT_INIATAREA ,convert(VARCHAR(24),t.DT_TERATAREA,103) AS DT_TERATAREA,t.TX_RESPATAREA,t.TX_UMAATAREA,t.TX_TIPOATAREA1,t.NUM_PRESUATAREA from ME_ATAREA t inner join ME_ACTPER ac on ac.NUM_CODACTPER_PK=t.NUM_CODACTPER_PK where t.NUM_CODACTPER_PK=?";

            pstmt = conn.prepareStatement(sql);

            pstmt.setInt(1, idAct);

            rs = pstmt.executeQuery();
            TActividadVO bean = null;
            while (rs.next()) {
                bean = new TActividadVO();
                setTareaPorActividadM(bean, rs);
                lista.add(bean);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }

        return lista;
    }


    



   /*   public OEstrategicoVO selectById(int id) throws SQLException {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        OEstrategicoVO bean = null;
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            String sql = "select NUM_CODOBJ_PK,TX_DESCOBJ from ME_OBJ where NUM_CODOBJ_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            bean = new OEstrategicoVO();
            if (rs.next()) {
                setOEstrategico(bean, rs);
            }
            rs.close();
            pstmt.close();
        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        }

        return bean;
    }
    *
    *
    */

}
